<?php
/*
 * Copyright 2009 Eat Local Food, LLC
 * Copyright (c) 2007 osCommerce (this file was written after
 * code review of osCommerce).
 *
 * This file is part of gwtCommerce.
 *
 * gwtCommerce is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 2 of the License, or
 * (at your option) any later version.
 *
 * gwtCommerce is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with gwtCommerce.  If not, see <http://www.gnu.org/licenses/>.
 */

define('IN_ADMIN_COMPONENT_CONTROLLER', 'true');

function orderStatus($orderId) 
{
	$sqlQuery = 'select orders_status from ' . TABLE_ORDERS . ' where orders_id=' . $orderId;
	$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
	$i = 0;
	$statusId = -1;
	while($row = mysql_fetch_array($dataReturned))
	{
		$statusId = (int)$row['orders_status'];
	}
	return $statusId;
}

function updateProduct($statusId, $add, $where, $new_quantity = 0) 
{
	$opOperator = ' - ';
	$qOperator = ' + ';
	if ($add) {
		$opOperator = ' + ';
		$qOperator = ' - ';
	}
	$addOrig = $add;

	if ($statusId > -1) 
	{
		$sqlQuery = 'select products_id, products_quantity from ' . TABLE_ORDERS_PRODUCTS . ' ' . $where;
		$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
		while($row = mysql_fetch_array($dataReturned))
		{
			$productId = (int)$row['products_id'];
			
			$quantity = (int)$row['products_quantity'];
			if ($new_quantity > 0)  {
				$quantity = $new_quantity - $quantity;
				if ($quantity < 0) {
					$add = !$addOrig;
					$quantity = -1 * $quantity;
					$opOperator = ' - ';
					$qOperator = ' + ';
					if ($add) {
						$opOperator = ' + ';
						$qOperator = ' - ';
					}
				}
			}

			$sql = 'update ' . TABLE_PRODUCTS . ' set products_ordered  = products_ordered' . $opOperator . $quantity . ' where products_id = ' . $productId;
			mysql_query($sql) or die(mysql_error());
			if (returnBoolean(STOCK_LIMITED))
			{
				$sql = 'update ' . TABLE_PRODUCTS . ' set products_quantity  = products_quantity' . $qOperator . $quantity .' where products_id = ' . $productId;
				mysql_query($sql) or die(mysql_error());
			}
		}
	}
}

class EditOrderComponent
{
	function EditOrderComponent()
	{
	}

	function action(&$params, $action)
	{
		if ($action == 'UpdateOrderHistory') 
			return $this->updateOrderHistory($params);
		else if ($action == 'DeleteOrder') 
			return $this->deleteOrder($params);
		else if ($action == 'EditIntroduction')
			return $this->editIntroduction($params);
		else if ($action == 'EditCustomer') 
			return $this->editAddress($params, 'customers');
		else if ($action == 'EditShipTo') 
			return $this->editAddress($params, 'delivery');
		else if ($action == 'EditBillTo') 
			return $this->editAddress($params, 'billing');
		else if ($action == 'DeleteProduct') 
			return $this->deleteProduct($params);
		else if ($action == 'EditProduct') 
			return $this->editProduct($params);
		else if ($action == 'DeleteTotal') 
			return $this->deleteTotal($params);
		else if ($action == 'AddTotal') 
			return $this->addTotal($params);
		else if ($action == 'AddCoupon') 
			return $this->addCoupon($params);
		else if ($action == 'UpdateTotals')
			return $this->updateTotals($params);
		else if ($action == 'UpdateCatalogProductsToCurrentPricing')
			return $this->updateCatalogProductsToCurrentPricing($params);
		else if ($action == 'AddPayment')
			return $this->addPayment($params);
		else if ($action == 'InsertQuote')
			return $this->insertQuote($params);
		else if ($action == 'DeleteTermAndCondition')
			return $this->deleteTermAndCondition($params);
		else if ($action == 'InsertTermAndCondition')
			return $this->insertTermAndCondition($params);
		else 
			return $this->query($params);
	}

	function query(&$params) 
	{
		$handle = get_class($this);
		$language_id = (int)$params['languageId'];
		$orderId = (int)$params['orderId'];
		$value{$handle . 'MetaData'}{'display_company'}= returnBoolean(ACCOUNT_COMPANY);
		$value{$handle . 'MetaData'}{'display_state'}= returnBoolean(ACCOUNT_STATE);
		$value{$handle . 'MetaData'}{'display_suburb'}= returnBoolean(ACCOUNT_SUBURB);
		$value{$handle . 'MetaData'}{'download_enabled'}= returnBoolean(DOWNLOAD_ENABLED);
		$value{$handle . 'MetaData'}{'download_maxdays'}= (int)DOWNLOAD_MAX_DAYS;
		$value{$handle . 'MetaData'}{'download_maxcount'}= (int)DOWNLOAD_MAX_COUNT;
		$value{$handle . 'MetaData'}{'do_not_edit_status_id'}= 5;

		if ($orderId > 0)
		{
			$sqlQuery = 'select o.orders_id, o.customers_id, o.customers_name, o.customers_company, o.customers_street_address, o.customers_suburb, o.customers_city, ' .
			'o.customers_postcode, o.customers_state, o.customers_country, o.customers_telephone, o.customers_email_address, ' .
			'o.delivery_name, o.delivery_company, o.delivery_street_address, o.delivery_suburb, o.delivery_city, o.delivery_postcode, o.delivery_state, ' .
			'o.delivery_country, o.billing_name, o.billing_company, o.billing_street_address, o.billing_suburb, o.billing_city, o.billing_postcode, ' . 
			"o.billing_state, o.billing_country, o.payment_method, DATE_FORMAT(o.last_modified, '%Y-%m-%d %H:%i:%s') as last_modified, " .
			"DATE_FORMAT(o.date_purchased, '%Y-%m-%d %H:%i:%s') as date_purchased, o.orders_status, os.orders_status_name, o.currency, o.currency_value, ot.value " .
			'from ' . TABLE_ORDERS . ' o, ' . TABLE_ORDERS_STATUS . ' os, ' . TABLE_ORDERS_TOTAL . ' ot ' .
			'where o.orders_id = \''. $orderId . '\' ';

			$sqlQuery .= 'and o.orders_status = os.orders_status_id ' .
			'and o.orders_id = ot.orders_id and ot.class = \'ot_total\' ' .
			'and os.language_id = \'' . $language_id . '\' ';
			$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
			$i = 0;

			while($row = mysql_fetch_array($dataReturned))
			{
				$orderAccountId=$row['customers_id'];
				$order_id = (int)$row['orders_id'];
				$status_id = (int)$row['orders_status'];
				$value{$handle}{$i}{'id'}= $order_id;
				$value{$handle}{$i}{'accountId'}=$orderAccountId;
				$value{$handle}{$i}{'name'}= $row['customers_name'];
				$value{$handle}{$i}{'company'}= $row['customers_company'];
				$value{$handle}{$i}{'street_address'}= $row['customers_street_address'];
				$value{$handle}{$i}{'suburb'}= $row['customers_suburb'];
				$value{$handle}{$i}{'city'}= $row['customers_city'];
				$value{$handle}{$i}{'postcode'}= $row['customers_postcode'];
				$value{$handle}{$i}{'state'}= $row['customers_state'];
				$value{$handle}{$i}{'country'}= $row['customers_country'];
				$value{$handle}{$i}{'telephone'}= $row['customers_telephone'];
				$value{$handle}{$i}{'email_address'}= $row['customers_email_address'];
				$value{$handle}{$i}{'ship_to_name'}= $row['delivery_name'];
				$value{$handle}{$i}{'ship_to_company'}= $row['delivery_company'];
				$value{$handle}{$i}{'ship_to_street_address'}= $row['delivery_street_address'];
				$value{$handle}{$i}{'ship_to_suburb'}= $row['delivery_suburb'];
				$value{$handle}{$i}{'ship_to_city'}= $row['delivery_city'];
				$value{$handle}{$i}{'ship_to_postcode'}= $row['delivery_postcode'];
				$value{$handle}{$i}{'ship_to_state'}= $row['delivery_state'];
				$value{$handle}{$i}{'ship_to_country'}= $row['delivery_country'];
				$value{$handle}{$i}{'bill_to_name'}= $row['billing_name'];
				$value{$handle}{$i}{'bill_to_company'}= $row['billing_company'];
				$value{$handle}{$i}{'bill_to_street_address'}= $row['billing_street_address'];
				$value{$handle}{$i}{'bill_to_suburb'}= $row['billing_suburb'];
				$value{$handle}{$i}{'bill_to_city'}= $row['billing_city'];
				$value{$handle}{$i}{'bill_to_postcode'}= $row['billing_postcode'];
				$value{$handle}{$i}{'bill_to_state'}= $row['billing_state'];
				$value{$handle}{$i}{'bill_to_country'}= $row['billing_country'];
				$value{$handle}{$i}{'payment_method'}= $row['payment_method'];
				$value{$handle}{$i}{'last_modified'}= $row['last_modified'];
				$value{$handle}{$i}{'date_purchased'}= $row['date_purchased'];
				$value{$handle}{$i}{'status_id'}= $status_id;
				$value{$handle}{$i}{'status'}= $row['orders_status_name'];
				$value{$handle}{$i}{'currency_code'}= $row['currency'];
				$value{$handle}{$i}{'currency_value'}= (double)$row['currency_value'];
				$value{$handle}{$i}{'value'}= (double)$row['value'];

				$sqlQuery1 = 'select ot.orders_total_id, ot.title, ot.value, ot.class ' .
				' from ' . TABLE_ORDERS_TOTAL . ' ot ' .
				'where ot.orders_id = \''. $order_id . '\' ' .
				'order by ot.sort_order';

				$dataReturned1 = mysql_query($sqlQuery1) or die(mysql_error());
				$i1 = 0;
				while($row1 = mysql_fetch_array($dataReturned1))
				{
					$value{$handle}{$i}{'totals'}{$i1}{'id'}= (int)$row1['orders_total_id'];
					$value{$handle}{$i}{'totals'}{$i1}{'title'}= $row1['title'];
					$value{$handle}{$i}{'totals'}{$i1}{'value'}= (double)$row1['value'];
					$value{$handle}{$i}{'totals'}{$i1}{'class'}= $row1['class'];
					$i1++;
				}

				$sqlQuery1 = "select orders_payment_id, payment_amount, DATE_FORMAT(payment_timestamp, '%Y-%m-%d %H:%i:%s') as timestamp, comments " .
				' from ' . TABLE_ORDERS_PAYMENTS . ' ' .
				'where orders_id = '. $order_id . ' ' .
				'order by orders_payment_id';

				$dataReturned1 = mysql_query($sqlQuery1) or die(mysql_error());
				$i1 = 0;
				while($row1 = mysql_fetch_array($dataReturned1))
				{
					$value{$handle}{$i}{'payments'}{$i1}{'id'}= (int)$row1['orders_payment_id'];
					$value{$handle}{$i}{'payments'}{$i1}{'amount'}= (double)$row1['payment_amount'];
					$value{$handle}{$i}{'payments'}{$i1}{'timestamp'}= $row1['timestamp'];
					$value{$handle}{$i}{'payments'}{$i1}{'comments'}= $row1['comments'];
					$i1++;
				}

				$sqlQuery1 = "select os.orders_status_name, osh.customer_notified, DATE_FORMAT(osh.date_added, '%Y-%m-%d %H:%i:%s') as date_added, osh.comments" .
				' from ' . TABLE_ORDERS_STATUS_HISTORY . ' osh, ' . TABLE_ORDERS_STATUS . ' os ' .
				'where osh.orders_id = '. $order_id . ' ' .
				'and osh.orders_status_id = os.orders_status_id ' .
				'order by osh.orders_status_history_id';

				$dataReturned1 = mysql_query($sqlQuery1) or die(mysql_error());
				$i1 = 0;
				while($row1 = mysql_fetch_array($dataReturned1))
				{
					$value{$handle}{$i}{'histories'}{$i1}{'status'}= $row1['orders_status_name'];
					$value{$handle}{$i}{'histories'}{$i1}{'date_added'}= $row1['date_added'];				
					$value{$handle}{$i}{'histories'}{$i1}{'comments'}= nl2br($row1['comments']);
					$value{$handle}{$i}{'histories'}{$i1}{'notified'}=(boolean)$row1['customer_notified'];
					$i1++;
				}

				$sqlQuery1 = 'select op.orders_products_id, op.products_id, op.products_model, op.products_name, ' .
				'op.products_price, op.final_price, op.products_tax, op.products_quantity, ' .
				'coalesce(p.products_weight, op.products_weight) as weight, ' .
				'coalesce(p.products_tax_class_id, op.products_tax_class_id) as tax_class_id ' .
				'from ' . TABLE_ORDERS_PRODUCTS . ' op ' .
				'left join ' . TABLE_PRODUCTS . ' p on (op.products_id = p.products_id) ' .
				'where op.orders_id = \''. $order_id . '\' ' .
				'order by op.products_name, op.orders_products_id';

				$dataReturned1 = mysql_query($sqlQuery1) or die(mysql_error());
				$i1 = 0;
				while($row1 = mysql_fetch_array($dataReturned1))
				{
					$opId = (int)$row1['orders_products_id'];
					$value{$handle}{$i}{'products'}{$i1}{'op_id'}= $opId;
					$value{$handle}{$i}{'products'}{$i1}{'id'}= (int)$row1['products_id'];
					$value{$handle}{$i}{'products'}{$i1}{'model'}= $row1['products_model'];				
					$value{$handle}{$i}{'products'}{$i1}{'name'}= $row1['products_name'];			
					$value{$handle}{$i}{'products'}{$i1}{'base_price'}= (double)$row1['products_price'];		
					$value{$handle}{$i}{'products'}{$i1}{'final_price'}= (double)$row1['final_price'];		
					$value{$handle}{$i}{'products'}{$i1}{'tax'}= (double)$row1['products_tax'];		
					$value{$handle}{$i}{'products'}{$i1}{'quantity'}= (double)$row1['products_quantity'];	
					$value{$handle}{$i}{'products'}{$i1}{'weight'}= (double)$row1['weight'];	
					$value{$handle}{$i}{'products'}{$i1}{'tax_class_id'}= (double)$row1['tax_class_id'];

					$sqlQuery2 = 'select opa.orders_products_attributes_id, opa.products_options, opa.products_options_values, opa.options_values_price, opa.price_prefix' .
					' from ' . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . ' opa ' .
					'where opa.orders_id = \'' . $orderId . '\' ' .
					'and opa.orders_products_id = \'' . $opId . '\' ' .
					'order by opa.orders_products_attributes_id';

					$dataReturned2 = mysql_query($sqlQuery2) or die(mysql_error());
					$i2 = 0;
					while($row2 = mysql_fetch_array($dataReturned2))
					{
						$value{$handle}{$i}{'products'}{$i1}{'attributes'}{$i2}{'id'}= (int)$row2['orders_products_attributes_id'];
						$value{$handle}{$i}{'products'}{$i1}{'attributes'}{$i2}{'values_id'}= 0;
						$value{$handle}{$i}{'products'}{$i1}{'attributes'}{$i2}{'name'}= $row2['products_options'];
						$value{$handle}{$i}{'products'}{$i1}{'attributes'}{$i2}{'values_name'}= $row2['products_options_values'];
						$value{$handle}{$i}{'products'}{$i1}{'attributes'}{$i2}{'values_price'}= (double)$row2['options_values_price'];
						$value{$handle}{$i}{'products'}{$i1}{'attributes'}{$i2}{'values_price_prefix'}= $row2['price_prefix'];
						$i2++;
					}

					// Download
					$sqlQuery2 = "select date_format(o.date_purchased, '%Y-%m-%d')" . ' as date_purchased_day, opd.download_count, ' .
					'opd.download_maxdays, opd.orders_products_filename, opd.orders_products_download_id, os.downloads_flag ' . 
					'from ' . TABLE_ORDERS . ' o, ' . TABLE_ORDERS_PRODUCTS . ' op, ' . TABLE_ORDERS_PRODUCTS_DOWNLOAD . ' opd, ' . TABLE_ORDERS_STATUS . ' os ' .
					'where op.orders_id = ' . $orderId . ' and op.orders_products_id = ' . $opId . ' ' .
					'and o.orders_id = op.orders_id and op.orders_products_id = opd.orders_products_id ' .
					'and o.orders_status = os.orders_status_id and os.language_id = ' . $language_id;
					$dataReturned2 = mysql_query($sqlQuery2) or die(mysql_error());
					$i2 = 0;
					while($row2 = mysql_fetch_array($dataReturned2))
					{
						$value{$handle}{$i}{'products'}{$i1}{'downloads'}{$i2}{'id'}= (int)$row2['orders_products_download_id'];
						$value{$handle}{$i}{'products'}{$i1}{'downloads'}{$i2}{'count'}= (int)$row2['download_count'];
						$value{$handle}{$i}{'products'}{$i1}{'downloads'}{$i2}{'days'}= (int)$row2['download_maxdays'];
						$value{$handle}{$i}{'products'}{$i1}{'downloads'}{$i2}{'filename'}= $row2['orders_products_filename'];

  						list($dt_year, $dt_month, $dt_day) = explode('-', $row2['date_purchased_day']);
  						$download_timestamp = mktime(23, 59, 59, $dt_month, $dt_day + $row2['download_maxdays'], $dt_year);

						$status = 'downloadable';
						$filepath = DIR_FS_DOWNLOAD . $row2['orders_products_filename'];
						$flag = (int)$row2['downloads_flag'];
						if ($flag != 1) $status = 'order status does not allow downloads';
						else if (($row2['download_maxdays'] != 0) && ($download_timestamp <= time())) $status = 'expired - days';
						else if ($row2['download_count'] <= 0) $status = 'expired - count';
						else if (!file_exists($filepath)) $status = 'file does not exist';

						$value{$handle}{$i}{'products'}{$i1}{'downloads'}{$i2}{'status'}= $status;

						$i2++;
					}
					$i1++;

				}

				if ($status_id < 0) {
					$sqlQuery1 = 'select introduction from ' . TABLE_QUOTES_INTRODUCTION . ' ' .
					'where orders_id = '. $order_id;
	
					$dataReturned1 = mysql_query($sqlQuery1) or die(mysql_error());
					while($row1 = mysql_fetch_array($dataReturned1))
					{
						$value{$handle}{$i}{'quote_introduction'} = nl2br($row1['introduction']);
						$value{$handle}{$i}{'quote_edit_introduction'} = $row1['introduction'];
						break;
					}

					//Terms and Conditions
					$sqlQuery = 'select tc.tandcs_id, tc.tandc_labels_id, tcl.label, tc.summary, tc.text, tc.required ' .
					'from ' . TABLE_TERMS_AND_CONDITIONS . ' tc, ' . 
					TABLE_TERMS_AND_CONDITIONS_LABELS . ' tcl, ' . 
					TABLE_QUOTES_TERMS_AND_CONDITIONS . ' qtc ' . 
					'where tc.tandc_labels_id=tcl.tandc_labels_id and tc.tandcs_id=qtc.tandcs_id and qtc.orders_id = ' . $orderId . ' ' .
					'order by tcl.sort_order, tc.sort_order';
					$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
					$i1 = 0;
					while($row = mysql_fetch_array($dataReturned))
					{
						$value{$handle}{$i}{'tandcs'}{$i1}{'id'}= (int)$row['tandcs_id'];
						$value{$handle}{$i}{'tandcs'}{$i1}{'label_id'}= (int)$row['tandc_labels_id'];
						$value{$handle}{$i}{'tandcs'}{$i1}{'label'}= $row['label'];
						$value{$handle}{$i}{'tandcs'}{$i1}{'summary'}= $row['summary'];
						$value{$handle}{$i}{'tandcs'}{$i1}{'text'}= nl2br($row['text']);
						$value{$handle}{$i}{'tandcs'}{$i1}{'required'}= (boolean)$row['required'];
						$i1++;
					}
				}

				$component = new AccountComponent();
				$subParams['accountId'] = $orderAccountId;
				$subParams['languageId'] = (int)$params['languageId'];
				$returnValue = $component->query($subParams);
				$value{$handle}{$i}{'account'} = $returnValue{'AccountComponent'}{0};
				$i++;
			}
			if ($i == 0)
			{
				$value{$handle}{0}{'id'}= -10;
			}
		}
		else
		{
			$value{$handle}{0}{'id'}= (int)$orderId;
		}
		return $value;
	}

	function editIntroduction(&$params) 
	{
		$orderId = (int)$params['orderId'];

		$sqlQuery = 'select orders_id from ' . TABLE_QUOTES_INTRODUCTION . ' where orders_id = '. $orderId;
		$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
		$exists = false;
		while($row = mysql_fetch_array($dataReturned))
		{
			$exists = true;
			break;
		}
		
		$action = 'insert';
		$where = '';
		$sql_data_array = array();
		$sql_data_array['introduction'] = trusted_prepare_input($params['quoteIntroduction']);
		if ($exists) {
			$action = 'update';
			$where = 'orders_id='.$orderId;
		}
		else {
			$sql_data_array['orders_id'] = $orderId;
		}
		db_perform(TABLE_QUOTES_INTRODUCTION, $sql_data_array, $action, $where);

		return $this->query($params);
	}

	function updateOrderHistory(&$params) 
	{
		$orderId = (int)$params['orderId'];
		$language_id = (int)$params['languageId'];
		$appendComment = (boolean)$params['appendComment'];
		$notifyCustomer = (boolean)$params['notifyCustomer'];
		$accountId = (int)$params['accountId'];
		$comment = trusted_prepare_input($params['comment']);
		if ($comment == null) $comment = '';
		$status = (int)$params['orderStatus'];

		$downloads_flag = 0;
		$sqlQuery = 'select downloads_flag from ' . TABLE_ORDERS_STATUS . ' where orders_status_id=' . $status;
		$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
		while($row = mysql_fetch_array($dataReturned))
		{
			$downloads_flag = $row['downloads_flag'];
			break;
		}

		$order_updated = false;
	
	        $sqlQuery = "select o.customers_name, o.customers_email_address, o.orders_status, DATE_FORMAT(o.date_purchased, '%Y-%m-%d %H:%i:%s') as date_purchased, " .
		'c.customers_isLogin, os.downloads_flag ' .
		'from ' . TABLE_ORDERS_STATUS . ' os, ' . TABLE_ORDERS . ' o left join ' . TABLE_CUSTOMERS . ' c on (o.customers_id = c.customers_id) ' .
		'where o.orders_id = ' . $orderId . ' and o.orders_status=os.orders_status_id';
		$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
		while($row = mysql_fetch_array($dataReturned))
		{
			$currStatus = $row['orders_status'];

			if ($currStatus != $status || strlen($comment) > 0) {

				if ($currStatus < 0 && $status > -1) {
					updateProduct($status, true, 'where orders_id=' . $orderId);
				}

				$curr_downloads_flag = (int)$row['downloads_flag'];
				if ($downloads_flag > -1 && $curr_downloads_flag < 0) {
					$sql = 'update ' . TABLE_ORDERS . ' set orders_status = ' . $status . ', last_modified = now(), date_purchased = now() where orders_id = ' . $orderId;
				}
				else {
					$sql = 'update ' . TABLE_ORDERS . ' set orders_status = ' . $status . ', last_modified = now() where orders_id = ' . $orderId;
				}

				mysql_query($sql) or die(mysql_error());
				
            			$customer_notified = '0';
				if ($notifyCustomer) {
					try {
					$statusName = 'Unknown';
					$sqlQuery1 = 'select orders_status_name from '. TABLE_ORDERS_STATUS . ' where orders_status_id = ' . $status;
					$dataReturned1 = mysql_query($sqlQuery1) or die(mysql_error());
					while($row1 = mysql_fetch_array($dataReturned1))
					{
						$statusName = $row1['orders_status_name'];
						break;
					}
					$appended_comments = '';

					$customers_isLogin = (int)$row['customers_isLogin'];
					if ($customers_isLogin == 0) $isLogin = false;
					else $isLogin = true;

					if ($appendComment) $appended_comments = sprintf(EMAIL_TEXT_COMMENTS_UPDATE, $comment) . "\n\n";

					$email = STORE_NAME . "\n" . EMAIL_SEPARATOR . "\n" . EMAIL_TEXT_ORDER_NUMBER . ' ' . $orderId . "\n";
					if ($isLogin) $email .= sprintf(EMAIL_TEXT_INVOICE_URL, HTTPS_SERVER . '#OrderComponent--' . $orderId) . "\n";
					$email .= EMAIL_TEXT_DATE_ORDERED . ' ' . strftime(DATE_FORMAT_LONG, strtotime($row['date_purchased'])) . "\n\n" .
					$appended_comments . 
					sprintf(EMAIL_TEXT_STATUS_UPDATE, $statusName);

					send_email($row['customers_name'], $row['customers_email_address'], EMAIL_TEXT_SUBJECT, $email, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS, false);
            				$customer_notified = '1';
					}
					catch(Exception $e)
  					{
  						$comment = 'Failed to email customer, Error: ' . $e->getMessage(). '\n\n Original Message:\n\n' . $comment;
  					}
				}
				
				$sql = 'insert into ' . TABLE_ORDERS_STATUS_HISTORY . ' (orders_id, orders_status_id, date_added, customer_notified, comments) values (' . $orderId . ', ' . $status . ', now(), ' . $customer_notified . ", '" . $comment  . "')";
				mysql_query($sql) or die(mysql_error());
			}
			break;
		}

		$params['action'] = 'query';
		return $this->query($params);
	}

	function deleteOrder(&$params) 
	{
		$orderId = (int)$params['orderId'];


		$sql = 'delete from ' . TABLE_ORDERS . ' where orders_id=' . $orderId;
		mysql_query($sql) or die(mysql_error());

		$statusId = orderStatus($orderId);
		updateProduct($statusId, false, 'where orders_id=' . $orderId);

		$sql = 'delete from ' . TABLE_ORDERS_PRODUCTS . ' where orders_id=' . $orderId;
		mysql_query($sql) or die(mysql_error());

		$sql = 'delete from ' . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . ' where orders_id=' . $orderId;
		mysql_query($sql) or die(mysql_error());

		$sql = 'delete from ' . TABLE_ORDERS_PRODUCTS_DOWNLOAD . ' where orders_id=' . $orderId;
		mysql_query($sql) or die(mysql_error());

		$sql = 'delete from ' . TABLE_ORDERS_STATUS_HISTORY . ' where orders_id=' . $orderId;
		mysql_query($sql) or die(mysql_error());

		$sql = 'delete from ' . TABLE_ORDERS_TOTAL . ' where orders_id=' . $orderId;
		mysql_query($sql) or die(mysql_error());

		$sql = 'delete from ' . TABLE_ORDERS_PAYMENTS . ' where orders_id=' . $orderId;
		mysql_query($sql) or die(mysql_error());

		clean_cache('AlsoPurchasedComponent');
		clean_cache('BestSellersComponent');

		$params['action'] = 'query';
		$params['orderId'] = -1;
		return $this->query($params);
	}

	function editAddress(&$params, $prefix) 
	{
		$orderId = (int)$params['orderId'];
		$addressId = (int)$params['addressId'];

		$sqlQuery = 'select customers_id from ' . TABLE_ORDERS . ' where orders_id=' . $orderId;
		$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
		$accountId = 0;
		while($row = mysql_fetch_array($dataReturned))
		{
			$accountId = (int)$row['customers_id'];
		}

		$addresses = getAddresses($accountId, 0);
		reset($addresses);
		while (list(,$address) = each($addresses))
		{
			if ($addressId == $address['id']) {
				$sql_data_array = array();
				$sql_data_array[$prefix . '_name'] = $address['firstname'] . ' ' . $address['lastname'];
				$sql_data_array[$prefix . '_company'] = $address['company'];
				$sql_data_array[$prefix . '_street_address'] = $address['street_address'];
				$sql_data_array[$prefix . '_suburb'] = $address['suburb'];
				$sql_data_array[$prefix . '_city'] = $address['city'];
				$sql_data_array[$prefix . '_postcode'] = $address['postcode'];
				$sql_data_array[$prefix . '_state'] = $address['state'];
				$sql_data_array[$prefix . '_country'] = $address['country'];
				$sql_data_array[$prefix . '_address_format_id'] = (int)$address['address_format_id'];
				$sql_data_array['last_modified'] = 'now()';
				db_perform(TABLE_ORDERS, $sql_data_array, 'update', 'orders_id='.$orderId);
			}
		}
		
		if ($prefix == 'customers') {
			$sql_data_array = array();
			$sql_data_array['customers_telephone'] = prepare_input($params['telephone']);
			$sql_data_array['customers_email_address'] = prepare_input($params['emailAddress']);
			$sql_data_array['last_modified'] = 'now()';
			db_perform(TABLE_ORDERS, $sql_data_array, 'update', 'orders_id='.$orderId);
		}

		$params['action'] = 'query';
		return $this->query($params);
	}

	function deleteProduct(&$params) 
	{
		$orderId = (int)$params['orderId'];
		$orderProductId = (int)$params['orderProductId'];

		$statusId = orderStatus($orderId);
		updateProduct($statusId, false, 'where orders_id=' . $orderId . ' and orders_products_id=' . $orderProductId);
		$sql = 'delete from ' . TABLE_ORDERS_PRODUCTS . ' where orders_id=' . $orderId . ' and orders_products_id=' . $orderProductId;
		mysql_query($sql) or die(mysql_error());

		$sql = 'delete from ' . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . ' where orders_id=' . $orderId . ' and orders_products_id=' . $orderProductId;
		mysql_query($sql) or die(mysql_error());

		$sql = 'delete from ' . TABLE_ORDERS_PRODUCTS_DOWNLOAD . ' where orders_id=' . $orderId . ' and orders_products_id=' . $orderProductId;
		mysql_query($sql) or die(mysql_error());

		$sqlQuery = 'select orders_status from ' . TABLE_ORDERS . ' where orders_id=' . $orderId;
		$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
		$status_id = 0;
		while($row = mysql_fetch_array($dataReturned))
		{
			$status_id = (int)$row['orders_status'];
		}
		if ($status_id < 0) return $this->updateCatalogProductsToCurrentPricing($params);

		return $this->updateTotals($params);
	}

	function editProduct(&$params) 
	{
		$orderId = (int)$params['orderId'];
		$statusId = orderStatus($orderId);
		$orderProductId = (int)$params['orderProductId'];
		$originalOrderProductId = $orderProductId;
		$productId = (int)$params['productId'];
		$language_id = (int)$params['languageId'];
		$quantity = (int)$params['productQuantity'];

		$sql_data_array = array();
		$sql_data_array['orders_id'] = $orderId;
		$sql_data_array['products_id'] = $productId;
		$sql_data_array['products_price'] = 0.0;
		$sql_data_array['final_price'] = 0.0;
		$sql_data_array['products_tax'] = 0.0;
		$sql_data_array['products_quantity'] = $quantity;
		if ($productId == 0) {
			$sql_data_array['products_model'] = prepare_input($params['productModel']);
			$sql_data_array['products_name'] = prepare_input($params['productName']);
			$sql_data_array['products_price'] = (double)$params['productPrice'];
			$sql_data_array['products_weight'] = (double)$params['productWeight'];
			$sql_data_array['products_tax_class_id'] = (int)$params['taxClassId'];
		}
		$sqlAction = 'insert';
		$sqlWhere = '';
		if ($orderProductId > 0) {
			$sql_data_array['products_model'] = prepare_input($params['productModel']);
			$sql_data_array['products_name'] = prepare_input($params['productName']);
			$sql_data_array['products_price'] = (double)$params['productPrice'];
			$sqlAction = 'update';
			$sqlWhere = 'orders_products_id='.$orderProductId;

			if ($productId > 0) {
				updateProduct($statusId, true, 'where orders_id=' . $orderId . ' and orders_products_id=' . $orderProductId, $quantity);
			}
		}
		else if ($orderProductId == 0 && $productId > 0) {
			$sqlQuery = 'select pd.products_name, p.products_model ' .
			'from ' . TABLE_PRODUCTS_DESCRIPTION . ' pd, ' . TABLE_PRODUCTS . ' p ' .
			'where p.products_id = pd.products_id and p.products_id='.$productId.' and pd.language_id = ' . $language_id;
			$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
			while($row = mysql_fetch_array($dataReturned))
			{
				$sql_data_array['products_model'] = $row['products_model'];
				$sql_data_array['products_name'] = $row['products_name'];
			}
		}

		db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array, $sqlAction, $sqlWhere);
		if ($sqlAction == 'insert') {
			$orderProductId = mysql_insert_id();
			if ($productId > 0) {
				updateProduct($statusId, true, 'where orders_id=' . $orderId . ' and orders_products_id=' . $orderProductId);
			}
		}
		if ($originalOrderProductId > 0 || $productId == 0)
		{
			$i = 1;
			while (true)
			{
				$attributeName = prepare_input($params['attributeName-'.$i]);
				if ($attributeName == null || strlen($attributeName == 0)) break;

				$opaId = (int)$params['attributeOrderProductId-'.$i];
				$is_delete = (boolean)$params['attributeDelete-'.$i];

	
				if($is_delete) {
					$sql = 'delete from ' . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . ' where orders_products_attributes_id=' . $opaId;
					mysql_query($sql) or die(mysql_error());
				}
				else {
					$sqlAction = 'insert';
					$sqlWhere = '';
					$sql_data_array = array();
					$sql_data_array['orders_id'] = $orderId;
					$sql_data_array['orders_products_id'] = $orderProductId;
					$sql_data_array['products_options'] = $attributeName;
					$sql_data_array['products_options_values'] = prepare_input($params['attributeValue-'.$i]);
					$sql_data_array['options_values_price'] = (double)$params['attributePrice-'.$i];
					$sql_data_array['price_prefix'] = prepare_input($params['attributePrefix-'.$i]);
					if ($opaId > 0) {
						$sqlAction = 'update';
						$sqlWhere = 'orders_products_attributes_id='.$opaId;
					}
					db_perform(TABLE_ORDERS_PRODUCTS_ATTRIBUTES, $sql_data_array, $sqlAction, $sqlWhere);
				}
				
				$i++;
				if ($i > 10) break;
			}

			$i = 1;
			while (true)
			{
				$opdId = (int)$params['downloadOrderProductId-'.$i];
				if ($opdId == 0) break;

				$is_delete = (boolean)$params['downloadDelete-'.$i];

	
				if($is_delete) {
	
					$sql = 'delete from ' . TABLE_ORDERS_PRODUCTS_DOWNLOAD . ' where orders_products_download_id=' . $opdId;
					mysql_query($sql) or die(mysql_error());
				}
				else {	 	
					$sql_data_array = array();
					$sql_data_array['orders_id'] = $orderId;
					$sql_data_array['orders_products_id'] = $orderProductId;
					$sql_data_array['orders_products_filename'] = prepare_input($params['downloadFilename-'.$i]);
					$sql_data_array['download_maxdays'] = (int)$params['downloadDays-'.$i];
					$sql_data_array['download_count'] = (int)$params['downloadCnt-'.$i];
					db_perform(TABLE_ORDERS_PRODUCTS_DOWNLOAD, $sql_data_array, 'update', 'orders_products_download_id=' . $opdId);
				}
				
				$i++;
				if ($i > 10) break;
			}
		}
		else
		{
			$i = 0;
			while (true)
			{
				$downloadEnabled = returnBoolean(DOWNLOAD_ENABLED);
				$attributesStr = $params['attributes-'.$i];
				if ($attributesStr == null || strlen($attributesStr == 0)) break;
				$attributes = explode ('_' , $attributesStr);
				$optionId = (int)$attributes[0];
				$valueId = (int)$attributes[1];

				if ($optionId > 0 && $valueId > 0)
				{
					$sqlQuery = 'select products_options_name, products_options_values_name, options_values_price, price_prefix, ' .
					'products_attributes_filename, products_attributes_maxdays, products_attributes_maxcount ' .
					'from ' . TABLE_PRODUCTS_OPTIONS . ' o, ' . TABLE_PRODUCTS_OPTIONS_VALUES . ' ov, ' . TABLE_PRODUCTS_ATTRIBUTES . ' a ' . 
					'left join ' . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . ' ad on (a.products_attributes_id = ad.products_attributes_id) ' .
					'where a.products_id=' . $productId . ' and a.options_id=' . $optionId . ' and a.options_values_id=' . $valueId . ' ' .
					'and a.options_id=o.products_options_id and a.options_values_id=ov.products_options_values_id ' .
					'and o.language_id=' . $language_id . ' and ov.language_id=' . $language_id;
					$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
					while($row = mysql_fetch_array($dataReturned))
					{
						$sql_data_array = array();
						$sql_data_array['orders_id'] = $orderId;
						$sql_data_array['orders_products_id'] = $orderProductId;
						$sql_data_array['products_options'] = $row['products_options_name'];
						$sql_data_array['products_options_values'] = $row['products_options_values_name'];
						$sql_data_array['options_values_price'] = $row['options_values_price'];
						$sql_data_array['price_prefix'] = $row['price_prefix'];
						db_perform(TABLE_ORDERS_PRODUCTS_ATTRIBUTES, $sql_data_array, 'insert');
						
        					//downloads?
        					if ($downloadEnabled) {
        						$filename = $row['products_attributes_filename'];
        						if ($filename != null && strlen($filename) > 0) {
        							$sql_data_array = array();
								$sql_data_array['orders_id'] = $orderId;
								$sql_data_array['orders_products_id'] = $orderProductId;
								$sql_data_array['orders_products_filename'] = $filename;
								$sql_data_array['download_maxdays'] = (int)$row['products_attributes_maxdays'];
								$sql_data_array['download_count'] = (int)$row['products_attributes_maxcount'];
								db_perform(TABLE_ORDERS_PRODUCTS_DOWNLOAD, $sql_data_array, 'insert'); 
        						}
        					}
						break;
					}
				}
				
				$i++;
				if ($i > 10) break;
			}
		}

		$sqlQuery = 'select orders_status from ' . TABLE_ORDERS . ' where orders_id=' . $orderId;
		$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
		$status_id = 0;
		while($row = mysql_fetch_array($dataReturned))
		{
			$status_id = (int)$row['orders_status'];
		}
		if ($status_id < 0) return $this->updateCatalogProductsToCurrentPricing($params);

		return $this->updateTotals($params);
	}

	function deleteTotal(&$params) 
	{
		$orderId = (int)$params['orderId'];
		$orderTotalId = (int)$params['orderTotalId'];

		$sqlQuery = 'select class from ' . TABLE_ORDERS_TOTAL .
		' where orders_id=' . $orderId . ' and orders_total_id=' . $orderTotalId;

		$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
		while($row = mysql_fetch_array($dataReturned))
		{
			$totalClass = $row['class'];
			if ($totalClass == 'ot_adhoc' || $totalClass == 'ot_coupon') {
				$sql = 'delete from ' . TABLE_ORDERS_TOTAL . 
				' where orders_id=' . $orderId . ' and orders_total_id=' . $orderTotalId;
				mysql_query($sql) or die(mysql_error());
				return $this->updateTotals($params);
			}
		}

		$params['action'] = 'query';
		return $this->query($params);
	}

	function addTotal(&$params) 
	{
		$orderId = (int)$params['orderId'];
		$value = (double)$params['totalValue'];
		$title = prepare_input($params['totalTitle']);

		$sqlQuery = 'select sort_order from ' . TABLE_ORDERS_TOTAL .
 		' where orders_id=' . $orderId . ' and class=\'ot_adhoc\'';

		$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
		$sort_order = 100;
		while($row = mysql_fetch_array($dataReturned))
		{
			$sort_order = (int)$row['sort_order'] + 1;
		}

		$sql_data_array = array();
		$sql_data_array['orders_id'] = $orderId;
		$sql_data_array['title'] = $title;
		$sql_data_array['text'] = (string)$value;
		$sql_data_array['value'] = $value;
		$sql_data_array['class'] = 'ot_adhoc';
		$sql_data_array['sort_order'] = $sort_order;
		db_perform(TABLE_ORDERS_TOTAL, $sql_data_array, 'insert', '');

		$sql = 'update ' . TABLE_ORDERS_TOTAL . ' set value  = value + '. $value . ", text = '', sort_order=1000 " .
		'where orders_id=' . $orderId . ' and class=\'ot_total\'';
		mysql_query($sql) or die(mysql_error());

		$params['action'] = 'query';
		return $this->query($params);
	}

	function addCoupon(&$params) 
	{
		return $this->updateTotals($params);
	}

	function updateCatalogProductsToCurrentPricing(&$params)
	{
		$orderId = (int)$params['orderId'];
		$sql_data_array = array();
		$sql_data_array['products_price'] = 0.0;
		db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array, 'update', 'orders_id='.$orderId.' and products_id > 0');
		return $this->updateTotals($params);
	}
	
	function updateTotals(&$params)
	{
		$orderId = (int)$params['orderId'];
		$sqlQuery = 'select ot.orders_total_id, ot.title, ot.value, ot.class ' .
		' from ' . TABLE_ORDERS_TOTAL . ' ot ' .
		'where ot.orders_id = \''. $orderId . '\' ' .
		'order by ot.sort_order';
		$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
		$i = 0;
		$currentTotals = Array();
		while($row = mysql_fetch_array($dataReturned))
		{
			$currentTotals{$i}['orders_total_id'] = (int)$row['orders_total_id'];
			$currentTotals{$i}['value'] = $row['value'];
			$currentTotals{$i}['title'] = $row['title'];
			$currentTotals{$i}['class'] = $row['class'];
			$currentTotals{$i}['match'] = false;
			$i++;
		}

		require_once('includes/components/CheckoutComponent.php');
		$checkout_component = new CheckoutComponent();
		$this->setup($params, $checkout_component);

		if ($this->errorCode != 0) return;

		$checkout_component->generateShipping($params);
		if ($checkout_component->errorCode != 0) return;

		$return = $checkout_component->order_total($params);
		if ($checkout_component->errorCode != 0) return;

		$totals = $return{'CheckoutComponent'}{0}{'totals'};
		
		reset($totals);
		$i = 1;
		$j = 500;
		while (list(,$total) = each($totals))
		{
			$match = false;
			reset($currentTotals);
			while (list(,$currentTotal) = each($currentTotals))
			{
				if (!$currentTotal['match'] && $currentTotal['class'] == $total['class']) {
					$match = true;
					$currentTotal['match'] = true;
					$sql_data_array = array();
					$sql_data_array['title'] = $total['title'];
					$sql_data_array['text'] = (string)$total['value'];
					$sql_data_array['value'] = $total['value'];
					if ($total['class'] == 'ot_total') {
						$sql_data_array['sort_order'] = 1000;
					}
					else if ($total['class'] == 'ot_adhoc') {
						$sql_data_array['sort_order'] = $j;
						$j++;
					}
					else {
						$sql_data_array['sort_order'] = $i;
						$i++;
					}
					db_perform(TABLE_ORDERS_TOTAL, $sql_data_array, 'update', 'orders_total_id=' . $currentTotal['orders_total_id']);
				}
			}
			if (!$match) {
				$sql_data_array = array();
				$sql_data_array['orders_id'] = $orderId;
				$sql_data_array['title'] = $total['title'];
				$sql_data_array['text'] = (string)$total['value'];
				$sql_data_array['value'] = $total['value'];
				$sql_data_array['class'] = $total['class'];
				$sql_data_array['sort_order'] = $i;
				db_perform(TABLE_ORDERS_TOTAL, $sql_data_array, 'insert', '');
				$i++;
			}
			
		}
		$cart = &$checkout_component->cart;
		$sz = sizeof($cart);
		for ($i=0; $i< $sz; $i++)
		{
			$opId = (int)$cart[$i]['op_id'];
			$sql_data_array = array();
			$sql_data_array['products_price'] = (double)$cart[$i]['products_price'];
			$sql_data_array['final_price'] = (double)$cart[$i]['cart_price'];
			$sql_data_array['products_tax'] = (double)$cart[$i]['tax_rate'];
			db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array, 'update', 'orders_products_id='.$opId);

			$attributes = &$cart[$i]['cart_options'];
			$sz1 = sizeof($attributes);
			for ($j=0; $j<$sz1; $j++)
			{
				$sql_data_array_attributes = array();
				$sql_data_array_attributes['options_values_price'] = $attributes[$j]['values_price'];
				$sql_data_array_attributes['price_prefix'] = $attributes[$j]['values_price_prefix'];
				db_perform(TABLE_ORDERS_PRODUCTS_ATTRIBUTES, $sql_data_array_attributes, 'update', 'orders_products_attributes_id='.$attributes[$j]['orders_products_attributes_id']);
			}

		}

		$sql_data_array = array();
		$sql_data_array['last_modified'] = 'now()';
		$sql_data_array['shipping_module'] = $checkout_component->shipping_module . "_" . $checkout_component->shipping_service;
		db_perform(TABLE_ORDERS, $sql_data_array, 'update', 'orders_id='.$orderId);

		$params['action'] = 'query';
		return $this->query($params);
	}

	/**
	 * make sure appropriate shipto, billto information is setup *
	 */
	function setup(&$params, &$checkout_component) 
	{
		$checkout_component->errorCode = 0;
		$checkout_component->errorMessage = '';
		$checkout_component->paymentErrorMessage = '';

		$language_id = (int)$params['languageId'];
		$orderId = (int)$params['orderId'];

		$i = 0;
		if ($orderId > 0)
		{
			$display_state = returnBoolean(ACCOUNT_STATE);

			//Ship From
			$shipFromCountryCode = SHIPPING_ORIGIN_COUNTRY;
			$sqlQuery = 'select countries_id, countries_name, countries_iso_code_2, countries_iso_code_3, address_format_id from ' . TABLE_COUNTRIES . ' where countries_id=' . $shipFromCountryCode;
			$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
			$i = 0;
			while($row = mysql_fetch_array($dataReturned))
			{
				$shipFrom['country'] = $row['countries_name'];
				$shipFrom['country_id'] = $row['countries_id'];
				$shipFrom['country_iso_code_2'] = $row['countries_iso_code_2'];
				$shipFrom['country_iso_code_3'] = $row['countries_iso_code_3'];
				$shipFrom['address_format_id'] = $row['address_format_id'];
			}
			$shipFrom['zone_id'] = STORE_ZONE;
			$shipFrom['postcode'] = SHIPPING_ORIGIN_ZIP;

       			if ($shipFrom['country_iso_code_2'] == 'US' && $shipFrom['postcode'] != null && strlen($shipFrom['postcode']) > 4) $shipFrom['postcode'] = substr($shipFrom['postcode'], 0, 5);

			$checkout_component->shipFrom = $shipFrom;

			$sqlQuery = 'select o.orders_id, o.customers_id, c.customers_isWholesale, o.customers_name, o.customers_company, o.customers_street_address, o.customers_suburb, o.customers_city, ' .
			'o.customers_postcode, o.customers_state, o.customers_country, o.customers_telephone, o.customers_email_address, ' .
			'o.delivery_name, o.delivery_company, o.delivery_street_address, o.delivery_suburb, o.delivery_city, o.delivery_postcode, o.delivery_state, ' .
			'o.delivery_country, o.billing_name, o.billing_company, o.billing_street_address, o.billing_suburb, o.billing_city, o.billing_postcode, ' . 
			"o.billing_state, o.billing_country, o.payment_method, DATE_FORMAT(o.last_modified, '%Y-%m-%d %H:%i:%s') as last_modified, " .
			"DATE_FORMAT(o.date_purchased, '%Y-%m-%d %H:%i:%s') as date_purchased, os.orders_status_name, o.currency, o.currency_value, ot.value, " .
			'c1.countries_id as c1_id, c1.countries_iso_code_2 as c1_iso_2, c1.countries_iso_code_3 as c1_iso_3, c1.address_format_id as c1_af, ' .
			'c2.countries_id as c2_id, c2.countries_iso_code_2 as c2_iso_2, c2.countries_iso_code_3 as c2_iso_3, c2.address_format_id as c2_af, ' .
			'c3.countries_id as c3_id, c3.countries_iso_code_3 as c3_iso_2, c3.countries_iso_code_3 as c3_iso_3, c3.address_format_id as c3_af ' .
			'from ' . TABLE_ORDERS . ' o, ' . TABLE_ORDERS_STATUS . ' os, ' . TABLE_ORDERS_TOTAL . ' ot, ' . TABLE_CUSTOMERS . ' c, ' .
			TABLE_COUNTRIES . ' c1, ' . TABLE_COUNTRIES . ' c2, ' . TABLE_COUNTRIES . ' c3 ' .
			'where o.orders_id = '. $orderId . ' ' .
			'and o.orders_status = os.orders_status_id ' .
			'and o.orders_id = ot.orders_id and ot.class = \'ot_total\' ' .
			'and os.language_id = ' . $language_id . ' ' .
			'and o.customers_id = c.customers_id ' .
			'and o.customers_country = c1.countries_name ' .
			'and o.delivery_country = c2.countries_name ' .
			'and o.billing_country = c3.countries_name ';
			$dataReturned = mysql_query($sqlQuery) or die(mysql_error());

			while($row = mysql_fetch_array($dataReturned))
			{
				//Account Info
				$zone_id = 0;
				if ($display_state && strlen($row['customers_state']) > 0)
				{
					$state = $row['customers_state'];
					$zone_identifier = 'zone_code';
					if (strlen($state) > 2) $zone_identifier = 'zone_name';
					$sqlQuery1 = 'select zone_id from ' . TABLE_ZONES . ' ' .
					'where zone_country_id = ' . (int)$row['c1_id'] . ' and ' . $zone_identifier . ' = \'' . $state . '\'';
					$dataReturned1 = mysql_query($sqlQuery1) or die(mysql_error());
					while($row1 = mysql_fetch_array($dataReturned1))
					{
						$zone_id = $row1['zone_id'];
					}
				}
				$params['accountId'] = $row['customers_id'];
				$accountInfo['name'] = $row['customers_name'];
				$accountInfo['company'] = $row['customers_company'];
				$accountInfo['street_address'] = $row['customers_street_address'];
				$accountInfo['suburb'] = $row['customers_suburb'];
				$accountInfo['postcode'] = $row['customers_postcode'];
				$accountInfo['city'] = $row['customers_city'];
				$accountInfo['state'] = $row['customers_state'];
				$accountInfo['zone_id'] = $zone_id;
				$accountInfo['country'] = $row['customers_country'];
				$accountInfo['country_id'] = $row['c1_id'];
				$accountInfo['country_iso_code_2'] = $row['c1_iso_2'];
				$accountInfo['country_iso_code_3'] = $row['c1_iso_3'];
				$accountInfo['address_format_id'] = $row['c1_af'];
				$accountInfo['email_address'] = $row['customers_email_address'];
				$accountInfo['telephone'] = $row['customers_telephone'];

				$wholesale = (int)$row['customers_isWholesale'];
				if ($wholesale == 0) $accountInfo['wholesale'] = false;
				else $accountInfo['wholesale'] = true;

				$checkout_component->accountInfo = $accountInfo;

				//Ship To
				$zone_id = 0;
				if ($display_state && strlen($row['delivery_state']) > 0)
				{
					$state = $row['delivery_state'];
					$zone_identifier = 'zone_code';
					if (strlen($state) > 2) $zone_identifier = 'zone_name';
					$sqlQuery1 = 'select zone_id from ' . TABLE_ZONES . ' ' .
					'where zone_country_id = ' . (int)$row['c2_id'] . ' and ' . $zone_identifier . ' = \'' . $state . '\'';
					$dataReturned1 = mysql_query($sqlQuery1) or die(mysql_error());
					while($row1 = mysql_fetch_array($dataReturned1))
					{
						$zone_id = $row1['zone_id'];
					}
				}
				$shipTo['name'] = $row['delivery_name'];
				$shipTo['company'] = $row['delivery_company'];
				$shipTo['street_address'] = $row['delivery_street_address'];
				$shipTo['suburb'] = $row['delivery_suburb'];
				$shipTo['postcode'] = $row['delivery_postcode'];
				$shipTo['city'] = $row['delivery_city'];
				$shipTo['state'] = $row['delivery_state'];
				$shipTo['zone_id'] = $zone_id;
				$shipTo['country'] = $row['delivery_country'];
				$shipTo['country_id'] = $row['c2_id'];
				$shipTo['country_iso_code_2'] = $row['c2_iso_2'];
				$shipTo['country_iso_code_3'] = $row['c2_iso_3'];
				$shipTo['address_format_id'] = $row['c2_af'];

       				if ($shipTo['country_iso_code_2'] == 'US' && $shipTo['postcode'] != null && strlen($shipTo['postcode']) > 4) $shipTo['postcode'] = substr($shipTo['postcode'], 0, 5);

				$checkout_component->shipTo = $shipTo;

				//Bill To
				$zone_id = 0;
				if ($display_state && strlen($row['billing_state']) > 0)
				{
					$state = $row['billing_state'];
					$zone_identifier = 'zone_code';
					if (strlen($state) > 2) $zone_identifier = 'zone_name';
					$sqlQuery1 = 'select zone_id from ' . TABLE_ZONES . ' ' .
					'where zone_country_id = ' . (int)$row['c3_id'] . ' and ' . $zone_identifier . ' = \'' . $state . '\'';
					$dataReturned1 = mysql_query($sqlQuery1) or die(mysql_error());
					while($row1 = mysql_fetch_array($dataReturned1))
					{
						$zone_id = $row1['zone_id'];
					}
				}

				$billTo['name'] = $row['billing_name'];
				$billTo['company'] = $row['billing_company'];
				$billTo['street_address'] = $row['billing_street_address'];
				$billTo['suburb'] = $row['billing_suburb'];
				$billTo['postcode'] = $row['billing_postcode'];
				$billTo['city'] = $row['billing_city'];
				$billTo['state'] = $row['billing_state'];
				$billTo['zone_id'] = $zone_id;
				$billTo['country'] = $row['billing_country'];
				$billTo['country_id'] = $row['c3_id'];
				$billTo['country_iso_code_2'] = $row['c3_iso_2'];
				$billTo['country_iso_code_3'] = $row['c3_iso_3'];
				$billTo['address_format_id'] = $row['c3_af'];

				$checkout_component->billTo = $billTo;

				//Taxes
				if ($shipTo['country'] != null && strlen($shipTo['country']) > 0)
				{
					$checkout_component->tax_country_id = (int)$shipTo['country_id'];
					$checkout_component->tax_zone_id = (int)$shipTo['zone_id'];
				}
				else if ($billTo['country'] != null && strlen($billTo['country']) > 0)
				{
					$checkout_component->tax_country_id = (int)$billTo['country_id'];
					$checkout_component->tax_zone_id = (int)$billTo['zone_id'];
				}
				else
				{
					$checkout_component->tax_country_id = 0;
					$checkout_component->tax_zone_id = 0;
				}

				$sqlQuery2 = 'select ot.orders_total_id, ot.title, ot.value, ot.class ' .
				' from ' . TABLE_ORDERS_TOTAL . ' ot ' .
				'where ot.orders_id = \''. $orderId . '\' ' .
				'order by ot.sort_order';
				$dataReturned2 = mysql_query($sqlQuery2) or die(mysql_error());
				$i2 = 0;
				while($row2 = mysql_fetch_array($dataReturned2))
				{
					$totalClass = $row2['class'];
					if ($totalClass == 'ot_coupon') {
						$title = $row2['title'];
						$pos1 = strpos ($title , '(');
						$pos2 = strpos ($title , ')');
						$len = $pos2 -($pos1 + 1);
						$coupon = substr($title, $pos1 + 1, $len);
						$params['coupon_code'] = $coupon;
					}
					else if ($totalClass == 'ot_adhoc') {
						$key = $totalClass . $i2;
						$checkout_component->cart_prices[$key] = array('class' => $totalClass, 'value' => (double)$row2['value'], 'title' => $row2['title']);
					}
					$i2++;
				}

				//Collect Cart Information 
				$value{'Cart'} = array();
				$sqlQuery1 = 'select op.orders_products_id, op.products_id, op.products_model, op.products_name, op.products_price, ' .
				'op.products_tax, op.products_quantity, ' .
				'coalesce(p.products_weight, op.products_weight) as weight, ' .
				'coalesce(p.products_virtual, 0) as virtual,' .
				'coalesce(p.products_tax_class_id, op.products_tax_class_id) as tax_class_id,' .
				'coalesce(p.products_price,op.products_price) as catalog_product_price,' .
				'coalesce(sp.specials_new_products_price,p.products_price,op.products_price) as special_price,' .
				'coalesce(p2dc.discount_categories_id, 0) as discount_categories_id ' .
				'from ' . TABLE_ORDERS_PRODUCTS . ' op ' .
				'left join ' . TABLE_PRODUCTS . ' p on (op.products_id = p.products_id) ' .
				'left join ' . TABLE_PRODUCTS_TO_DISCOUNT_CATEGORIES . ' p2dc on (op.products_id = p2dc.products_id) ' .
				'left join ' . TABLE_SPECIALS . ' sp on (op.products_id = sp.products_id and sp.status = \'1\' and ((now() < sp.expires_date and sp.expires_date > 0) or sp.expires_date = 0)) ' .
				'where op.orders_id = '. $orderId . ' ' .
				'order by op.products_name, op.orders_products_id';
				$dataReturned1 = mysql_query($sqlQuery1) or die(mysql_error());
				$i1 = 0;
				while($row1 = mysql_fetch_array($dataReturned1))
				{
					$opId = (int)$row1['orders_products_id'];
					$product_id = (int)$row1['products_id'];
					$price = (double)$row1['products_price'];
					$catalogPrice = (double)$row1['catalog_product_price'];
					$useCatalog = false;
					if ($price == 0.0 && $product_id > 0) $useCatalog = true;
					
					$value{'Cart'}{$i1}{'op_id'}= $opId;
					$value{'Cart'}{$i1}{'id'}= $product_id;
					$value{'Cart'}{$i1}{'model'}= $row1['products_model'];				
					$value{'Cart'}{$i1}{'name'}= $row1['products_name'];
					$value{'Cart'}{$i1}{'tax'}= (double)$row1['products_tax'];		
					$value{'Cart'}{$i1}{'cart_qty'}= (double)$row1['products_quantity'];
					$value{'Cart'}{$i1}{'weight'}= (double)$row1['weight'];
					$value{'Cart'}{$i1}{'virtual'}= (boolean)$row1['virtual'];
					$value{'Cart'}{$i1}{'tax_class_id'}= (int)$row1['tax_class_id'];

					if ($useCatalog) {			
						$value{'Cart'}{$i1}{'price'}= $catalogPrice;
						$value{'Cart'}{$i1}{'special_price'}= (double)$row1['special_price'];	
						$value{'Cart'}{$i1}{'discount_categories_id'}= $row1['discount_categories_id'];	

						$sqlQuery2 = 'select products_price, products_qty ' .
						'from  ' . TABLE_PRODUCTS_PRICE_BREAK . ' ' .
						'where products_id = \'' . $product_id . '\' ' .
						'order by products_qty';

						$dataReturned2 = mysql_query($sqlQuery2) or die(mysql_error());
						$i2 = 0;
						while($row2 = mysql_fetch_array($dataReturned2))
						{
							$value{'Cart'}{$i1}{'price_breaks'}{$i2}{'price'}= (double)$row2['products_price'];
							$value{'Cart'}{$i1}{'price_breaks'}{$i2}{'quantity'}= (int)$row2['products_qty'];
							$i2++;
						}

						$sqlQuery2 = 'select  opa.orders_products_attributes_id, pa.products_attributes_id, po.products_options_id, po.products_options_name, '. 
						'pov.products_options_values_id, opa.products_options_values, pov.products_options_values_name, pa.options_values_price, pa.price_prefix ' .
						'from ' . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . ' opa, ' . TABLE_PRODUCTS_OPTIONS . ' po, ' . TABLE_PRODUCTS_OPTIONS_VALUES . ' pov, ' . TABLE_PRODUCTS_ATTRIBUTES . ' pa ' .
						'where opa.orders_id = \'' . $orderId . '\' ' .
						'and opa.orders_products_id = \'' . $opId . '\' ' .
						'and pa.products_id = \'' . $product_id . '\' ' .
						'and pa.options_id = po.products_options_id and po.language_id = \'' . $language_id . '\' ' .
						'and pa.options_values_id = pov.products_options_values_id and pov.language_id = \'' . $language_id . '\' ' .
						'and opa.products_options = po.products_options_name ' .
						'order by products_options_name, pa.attribute_sort, pov.products_options_values_name';
						$dataReturned2 = mysql_query($sqlQuery2) or die(mysql_error());

						$i2 = 0;
						while($row2 = mysql_fetch_array($dataReturned2))
						{
							$opvaluename = $row2['products_options_values_name'];
							$orderopvaluename = $row2['products_options_values'];
							$pos = strpos($orderopvaluename, $opvaluename);
							if ($pos !== false && $pos == 0) {
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'orders_products_attributes_id'} = $row2['orders_products_attributes_id'];
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'row_id'}= (int)$row2['products_attributes_id'];
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'product_id'}= $product_id;
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'id'}= (int)$row2['products_options_id'];
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'name'}= $row2['products_options_name'];
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'values_id'}= (int)$row2['products_options_values_id'];
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'values_name'}= $row2['products_options_values_name'];
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'values_price'}= (double)$row2['options_values_price'];
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'values_price_prefix'}= $row2['price_prefix'];
							$i2++;
							}
						}
					}
					else {		
						$value{'Cart'}{$i1}{'price'}= $price;
						$value{'Cart'}{$i1}{'special_price'}= $price;	
						$value{'Cart'}{$i1}{'discount_categories_id'}= 0;

						$sqlQuery2 = 'select opa.orders_products_attributes_id, opa.products_options, opa.products_options_values, opa.options_values_price, opa.price_prefix' .
						' from ' . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . ' opa ' .
						'where opa.orders_id = \'' . $orderId . '\' ' .
						'and opa.orders_products_id = \'' . $opId . '\' ' .
						'order by opa.orders_products_attributes_id';
	
						$dataReturned2 = mysql_query($sqlQuery2) or die(mysql_error());
						$i2 = 0;
						while($row2 = mysql_fetch_array($dataReturned2))
						{
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'orders_products_attributes_id'} = $row2['orders_products_attributes_id'];
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'product_id'}= $product_id;
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'id'}= 0;
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'name'}= $row2['products_options'];
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'values_id'}= 0;
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'values_name'}= $row2['products_options_values'];
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'values_price'}= (double)$row2['options_values_price'];
							$value{'Cart'}{$i1}{'cart_options'}{$i2}{'values_price_prefix'}= $row2['price_prefix'];
							$i2++;
						}
					}
					$i1++;
				}

				$checkout_component->cart = $value['Cart'];
				$i++;
			}
		}

		if ($orderId== 0 || $i == 0)
		{
			//missing information
			$this->errorCode = -5;
			$this->errorMessage = 'order information missing';
		}

		return $params;
	}

	function addPayment(&$params) 
	{
		$orderId = (int)$params['orderId'];
		if ($orderId > 0) {
			$sql_data_array = array();
			$sql_data_array['orders_id'] = $orderId;
			$sql_data_array['payment_amount'] = (double)$params['paymentAmount'];
			$sql_data_array['payment_timestamp'] = 'now()';
			$sql_data_array['comments'] = prepare_input($params['paymentComments']);
			db_perform(TABLE_ORDERS_PAYMENTS, $sql_data_array, 'insert');
		}
		$params['action'] = 'query';
		return $this->query($params);
	}

	function deleteTermAndCondition(&$params) 
	{
		$orderId = (int)$params['orderId'];
		$tAndCId = (int)$params['tAndCId'];

		$sql = 'delete from ' . TABLE_QUOTES_TERMS_AND_CONDITIONS . ' where tandcs_id=' . $tAndCId . ' and orders_id=' . $orderId;
		mysql_query($sql) or die(mysql_error());

		$params['action'] = 'query';
		return $this->query($params);
	}

	function insertTermAndCondition(&$params) 
	{
		$orderId = (int)$params['orderId'];

		$i = 0;
		while (true)
		{
			$i++;
			if ($i > 1000) break;

			$tAndCId = (int)$params['tAndCId-'.$i];
			if ($tAndCId == 0) break;
			
			$sql_data_array = array();
			$sql_data_array['orders_id'] = $orderId;
			$sql_data_array['tandcs_id'] = $tAndCId;
			db_perform(TABLE_QUOTES_TERMS_AND_CONDITIONS, $sql_data_array, 'insert');
		}

		$params['action'] = 'query';
		return $this->query($params);
	}

	function insertQuote(&$params) 
	{
		$accountId = (int)$params['customerId'];

		$sql_data_array = array();
		$sql_data_array['customers_id'] = $accountId;
		$sql_data_array['payment_method'] = "";
		$sql_data_array['shipping_module'] = "";
		$sql_data_array['orders_status'] = -1;
		$sql_data_array['date_purchased'] = 'now()';
		$sql_data_array['last_modified'] = 'now()';

		//Setup Default Currency
		$sqlQuery = 'select value from ' . TABLE_CURRENCIES . ' where code=\'' . DEFAULT_CURRENCY . '\'';
		$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
		$currency = array();
		while($row = mysql_fetch_array($dataReturned))
		{
			$sql_data_array['currency'] = DEFAULT_CURRENCY;
			$sql_data_array['currency_value'] = (double)$row['value'];
			break;
		}
		$this->currency = &$currency;

		//Get Telephone, Email, Default Address
		$sqlQuery = 'select customers_email_address, customers_default_address_id, customers_telephone ' .
		'from ' . TABLE_CUSTOMERS . 
		' where customers_id=' . $accountId;
		$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
		while($row = mysql_fetch_array($dataReturned))
		{
			$sql_data_array['customers_telephone'] = $row['customers_telephone'];
			$sql_data_array['customers_email_address'] = $row['customers_email_address'];
			$default_address_id = $row['customers_default_address_id'];
		}

		$addresses = getAddresses($accountId, $default_address_id);

		reset($addresses);
		while (list(,$address) = each($addresses))
		{
			if ($address['default']) {
				$name = $address['firstname'] . ' ' . $address['lastname'];
				$sql_data_array['customers_name'] = $name;
				$sql_data_array['delivery_name'] = $name;
				$sql_data_array['billing_name'] = $name;

				$sql_data_array['customers_company'] = $address['company'];
				$sql_data_array['delivery_company'] = $address['company'];
				$sql_data_array['billing_company'] = $address['company'];

				$sql_data_array['customers_street_address'] = $address['street_address'];
				$sql_data_array['delivery_street_address'] = $address['street_address'];
				$sql_data_array['billing_street_address'] = $address['street_address'];

				$sql_data_array['customers_suburb'] = $address['suburb'];
				$sql_data_array['delivery_suburb'] = $address['suburb'];
				$sql_data_array['billing_suburb'] = $address['suburb'];

				$sql_data_array['customers_city'] = $address['city'];
				$sql_data_array['delivery_city'] = $address['city'];
				$sql_data_array['billing_city'] = $address['city'];

				$sql_data_array['customers_postcode'] = $address['postcode'];
				$sql_data_array['delivery_postcode'] = $address['postcode'];
				$sql_data_array['billing_postcode'] = $address['postcode'];

				$sql_data_array['customers_state'] = $address['state'];
				$sql_data_array['delivery_state'] = $address['state'];
				$sql_data_array['billing_state'] = $address['state'];

				$sql_data_array['customers_country'] = $address['country'];
				$sql_data_array['delivery_country'] = $address['country'];
				$sql_data_array['billing_country'] = $address['country'];

				$sql_data_array['customers_address_format_id'] = (int)$address['address_format_id'];
				$sql_data_array['delivery_address_format_id'] = (int)$address['address_format_id'];
				$sql_data_array['billing_address_format_id'] = (int)$address['address_format_id'];
			}
		}
		
		db_perform(TABLE_ORDERS, $sql_data_array, 'insert');
		$orderId = mysql_insert_id();

		//Orders Status History
		$sql_data_array = array();
		$sql_data_array['orders_id'] = $orderId;
		$sql_data_array['orders_status_id'] = -1;
		$sql_data_array['date_added'] = 'now()';
		$sql_data_array['customer_notified'] = 0;
		$sql_data_array['comments'] = 'Quote created';
		db_perform(TABLE_ORDERS_STATUS_HISTORY, $sql_data_array, 'insert');

		//Orders Total
		$sql_data_array = array();
		$sql_data_array['orders_id'] = $orderId;
		$sql_data_array['title'] = 'Total:';
		$sql_data_array['text'] = '0.00';
		$sql_data_array['value'] = 0.0;
		$sql_data_array['class'] = 'ot_total';
		$sql_data_array['sort_order'] = 1000;
		db_perform(TABLE_ORDERS_TOTAL, $sql_data_array, 'insert');
		
		//Terms and Conditions
		$sqlQuery = 'select tc.tandcs_id ' .
		'from ' . TABLE_TERMS_AND_CONDITIONS . ' tc, ' . TABLE_TERMS_AND_CONDITIONS_LABELS . ' tcl ' .
		'where tc.tandc_labels_id=tcl.tandc_labels_id and tc.required=1 ' .
		'order by tcl.sort_order, tc.sort_order';
		$dataReturned = mysql_query($sqlQuery) or die(mysql_error());
		$i = 0;
		while($row = mysql_fetch_array($dataReturned))
		{
			$sql_data_array = array();
			$sql_data_array['orders_id'] = $orderId;
			$sql_data_array['tandcs_id'] = $row['tandcs_id'];
			db_perform(TABLE_QUOTES_TERMS_AND_CONDITIONS, $sql_data_array, 'insert');
		}

		$params['orderId'] = $orderId;
		return $this->updateTotals($params);
	}
}
?>
